import {
  CommandType,
  ICommand,
  ICommandService,
  ILogService,
  IUniverInstanceService,
  UniverInstanceType,
  Workbook,
} from '@univerjs/core';
import {
  expandToContinuousRange,
  SetRangeValuesMutation,
  ISetRangeValuesMutationParams,
} from '@univerjs/sheets';

function numberToExcelColumnName(num: number): string {
  let columnName = '';
  while (num >= 0) {
    const remainder = num % 26;
    columnName = String.fromCharCode(65 + remainder) + columnName;
    num = Math.floor(num / 26) - 1;
  }
  return columnName;
}

export interface IInsertRecordCommandParams {
  workbookId: string; // 工作簿 ID
  // 这里可以定义需要的参数
  // 比如书名、店铺、销售数量、单价等
  bookName: string;
  store: string;
  quantity: number;
  price: number;
}

// 在整个表格的最底部增加一个新的销售记录行
// 目前数据是随机生成的
// 数据结构是 书名 - 店铺 - 销售数量 - 单价 - 销售额
export const InsertRecordCommand: ICommand<IInsertRecordCommandParams> = {
  id: 'mybiz.command.insert-record',
  type: CommandType.COMMAND,
  handler: async (accessor, params) => {
    const { workbookId, bookName, store, quantity, price } = params;

    const univerInstanceService = accessor.get(IUniverInstanceService);
    const logService = accessor.get(ILogService);

    const workbook = univerInstanceService.getUnit<Workbook>(
      workbookId,
      UniverInstanceType.UNIVER_SHEET,
    );

    if (!workbook) {
      logService.error(
        `InsertRecordCommand: Workbook with ID ${workbookId} not found.`,
      );

      return false;
    }

    const worksheet = workbook.getActiveSheet();

    // 查找最后一行空着的表格
    const existingRange = expandToContinuousRange(
      { startRow: 0, endRow: 0, startColumn: 0, endColumn: 0 },
      { right: true, down: true },
      worksheet,
    );
    const nextRow = existingRange.endRow + 1;
    if (nextRow > worksheet.getMaxRows()) {
      logService.error(
        `InsertRecordCommand: Cannot insert record, maximum rows exceeded in worksheet ${worksheet.getSheetId()}.`,
      );
      return false;
    }

    // 在工作表的下一行插入数据
    const commandService = accessor.get(ICommandService);
    return commandService.executeCommand<ISetRangeValuesMutationParams>(
      SetRangeValuesMutation.id,
      {
        subUnitId: worksheet.getSheetId(),
        unitId: workbookId,
        cellValue: {
          [nextRow]: {
            0: { v: bookName }, // 书名
            1: { v: store }, // 店铺
            2: { v: quantity }, // 销售数量
            3: { v: price }, // 单价
            // 用公式计算销售额
            4: {
              f: `=${numberToExcelColumnName(2)}${nextRow}*${numberToExcelColumnName(3)}${nextRow}`,
            },
          },
        },
      },
    );
  },
};
